package com.pension.model.dao;

import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

import com.pension.model.dto.Reservation;
import com.pension.model.dto.Reservationoption;
import com.pension.model.dto.Roomoption;

public class ReservationDao {
	
	public void insertReservation(Reservation reservation) {
		
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		
		// 테스트용 SQL
//		set autocommit = 0;
//
//		begin;
//
//		INSERT INTO reservation (usepeople, startdate, days, memberid, roomnumber, roomsstatus, deletedroom, pricetotal) VALUES (3, -1, 2, 'admin', '102', 1, 0 ,198000);
//
//		INSERT INTO reservationoption (resernumber, optionno) VALUES (LAST_INSERT_ID(), 1);
//		INSERT INTO reservationoption (resernumber, optionno) VALUES (LAST_INSERT_ID(), 2);
//
//		commit;
		
		try {
			conn = ConnectionHelper.getConnection();
			
			conn.setAutoCommit(false);
			
			StringBuilder sql = new StringBuilder(256);
			sql.append("INSERT INTO reservation ")
			   .append("(usepeople, startdate, days, memberid, roomnumber, roomsstatus, deletedroom, pricetotal) ")
			   .append("VALUES ")
			   .append("(?, ?, ?, ?, ?, ?, ? ,?)");
			pstmt = conn.prepareStatement(sql.toString());
			
			pstmt.setInt(1, reservation.getUsepeople());
			pstmt.setDate(2, reservation.getStartdate());
			pstmt.setInt(3, reservation.getDays());
			pstmt.setString(4, reservation.getMemberid());
			pstmt.setInt(5, reservation.getRoomnumber());
			pstmt.setInt(6, reservation.getRoomsstatus());
			pstmt.setBoolean(7, reservation.isDeletedroom());
			pstmt.setInt(8, reservation.getPricetotal());
			
			pstmt.executeUpdate();
			pstmt.close();

			// 미완성, 테스트 및 구현 요망
			List<Roomoption> optionList = reservation.getOptions();
			
			if ( !optionList.isEmpty() )
			{
				for ( Roomoption op : optionList)
				{
					System.out.println("DB 옵션삽입 루프 : " + op);
					
					if ( op != null )
					{
						sql.delete(0, sql.length());
						
						sql.append("INSERT INTO reservationoption ")
						   .append("(resernumber, optionno) ")
						   .append("VALUES ")
						   .append("(LAST_INSERT_ID(), ?)");
						pstmt = conn.prepareStatement(sql.toString());
						
						pstmt.setInt(1, op.getOptionno());
						
						pstmt.executeUpdate();
						pstmt.close();
					}
				}
			}
			
			conn.commit();
			
		} catch (Exception ex) {
			try { conn.rollback(); } catch (Exception ex2) {}
			ex.printStackTrace();
		} finally {
			//6. 연결닫기
			if (rs != null) try { rs.close(); } catch (Exception ex) {}
			try { pstmt.close(); } catch (Exception ex) {}
			try { conn.setAutoCommit(true); } catch (Exception ex) {}
			try { conn.close(); } catch (Exception ex) {}
		}
	}
	
	public Reservation getReservationByResernumber(int resernumber)
	{
		Connection conn = null;
		PreparedStatement pstmt = null, pstmt2 = null;
		ResultSet rs = null, rs2 = null;
		Reservation reser = null;
		
		try {
			conn = ConnectionHelper.getConnection();
			StringBuffer sql = new StringBuffer(300);
			sql.append("SELECT "); 
			sql.append("resernumber, usepeople, startdate, days, ");
			sql.append("regdate, memberid, roomnumber, roomsstatus, ");
			sql.append("deletedroom, pricetotal ");
			sql.append("FROM reservation ");
			sql.append("WHERE resernumber = ? AND deletedroom = false");
			pstmt = conn.prepareStatement(sql.toString());
			pstmt.setInt(1, resernumber);
			rs = pstmt.executeQuery();
			if (rs.next()) {
				reser = new Reservation();
				reser.setResernumber(rs.getInt(1));
				reser.setUsepeople(rs.getInt(2));
				reser.setStartdate(rs.getDate(3));
				reser.setDays(rs.getInt(4));
				reser.setRegdate(rs.getDate(5));
				reser.setMemberid(rs.getString(6));
				reser.setRoomnumber(rs.getInt(7));
				reser.setRoomsstatus(rs.getInt(8));
				reser.setDeletedroom(rs.getBoolean(9));
				reser.setPricetotal(rs.getInt(10));
				
				// 미완성, 테스트 및 구현 요망
				// 일단 Subquery와 any로 구현.. 이 질의가 최적이라고는 생각되지는 않는다.
				ArrayList<Roomoption> opList = new ArrayList<Roomoption>();
				
				sql.delete(0, sql.length());
				
				sql.append("SELECT ");
				sql.append(" optionno, optionname, priceoption ");
				sql.append("FROM roomsoption ");
				sql.append("WHERE optionno = ");
				sql.append("any(SELECT optionno FROM reservationoption WHERE resernumber = ?)");
				pstmt2 = conn.prepareStatement(sql.toString());
				pstmt2.setInt(1, reser.getResernumber());
				rs2 = pstmt2.executeQuery();
				if (rs2.next()) {
					
					Roomoption roomoption = new Roomoption();
					
					roomoption.setOptionno(rs2.getInt(1));
					roomoption.setOptionname(rs2.getString(2));
					roomoption.setPriceoption(rs2.getInt(3));
					
					opList.add(roomoption);
				}
				
				if ( !opList.isEmpty() ) {
					reser.setOptions(opList);
				}
			}
			
		} catch (Exception ex) {
			ex.printStackTrace();
		} finally {
			try { if (rs2 != null) rs2.close(); } catch (Exception ex) { }
			try { if (pstmt2 != null) pstmt2.close(); } catch (Exception ex) { }
			try { if (rs != null) rs.close(); } catch (Exception ex) { }
			try { if (pstmt != null) pstmt.close(); } catch (Exception ex) { }
			try { if (conn != null) conn.close(); } catch (Exception ex) { }
		}
		
		return reser;
	}

	public List<Reservation> getSuitableDateList(String start, String end)
	{
		Connection conn = null;
		PreparedStatement pstmt = null, pstmt2 = null;
		ResultSet rs = null, rs2 = null;
		List<Reservation> reserList = new ArrayList<Reservation>();
		
//		SELECT 
//		resernumber, usepeople, startdate, days, 
//		regdate, memberid, roomnumber, roomsstatus, 
//		deletedroom, pricetotal 
//		FROM reservation 
//		WHERE startdate between '2014-04-01' and '2014-05-01'
		
		// 제작 중, 일단 끝에 걸치는 애들만 검색
		try {
			conn = ConnectionHelper.getConnection();
			StringBuffer sql = new StringBuffer(300);
			sql.append("SELECT "); 
			sql.append("resernumber, usepeople, startdate, days, ");
			sql.append("regdate, memberid, roomnumber, roomsstatus, ");
			sql.append("deletedroom, pricetotal ");
			sql.append("FROM reservation ");
			sql.append("WHERE startdate between ? - ( days + 1 ) and ?");
			pstmt = conn.prepareStatement(sql.toString());
			pstmt.setString(1, start);
			pstmt.setString(2, end);
			rs = pstmt.executeQuery();
			//System.out.println(start + " ~ " + end + "  " ); // + "  기간 정의간 메타데이터 : " + pstmt.getMetaData().toString() + " " + pstmt.getResultSet().toString());
			while (rs.next()) {
				Reservation reser = new Reservation();
				reser.setResernumber(rs.getInt(1));
				reser.setUsepeople(rs.getInt(2));
				reser.setStartdate(rs.getDate(3));
				reser.setDays(rs.getInt(4));
				reser.setRegdate(rs.getDate(5));
				reser.setMemberid(rs.getString(6));
				reser.setRoomnumber(rs.getInt(7));
				reser.setRoomsstatus(rs.getInt(8));
				reser.setDeletedroom(rs.getBoolean(9));
				reser.setPricetotal(rs.getInt(10));
				
				//System.out.println("sql질의 도중 조건에 맞는 객체 발견. : " + reser.getResernumber());
				
				// 미완성, 테스트 및 구현 요망
				// 일단 Subquery와 any로 구현.. 이 질의가 최적이라고는 생각되지는 않는다.
				
				ArrayList<Roomoption> opList = new ArrayList<Roomoption>();
				
				sql.delete(0, sql.length());
				
				sql.append("SELECT ");
				sql.append(" optionno, optionname, priceoption ");
				sql.append("FROM roomsoption ");
				sql.append("WHERE optionno = ");
				sql.append("any(SELECT optionno FROM reservationoption WHERE resernumber = ?)");
				pstmt2 = conn.prepareStatement(sql.toString());
				pstmt2.setInt(1, reser.getResernumber());
				rs2 = pstmt2.executeQuery();
				while (rs2.next()) {
					
					Roomoption roomoption = new Roomoption();
					
					roomoption.setOptionno(rs2.getInt(1));
					roomoption.setOptionname(rs2.getString(2));
					roomoption.setPriceoption(rs2.getInt(3));
					
					opList.add(roomoption);
				}
				
				if ( !opList.isEmpty() ) {
					reser.setOptions(opList);
				}
				
				reserList.add(reser);
			}
			
		} catch (Exception ex) {
			ex.printStackTrace();
		} finally {
			try { if (rs2 != null) rs2.close(); } catch (Exception ex) { }
			try { if (pstmt2 != null) pstmt2.close(); } catch (Exception ex) { }
			try { if (rs != null) rs.close(); } catch (Exception ex) { }
			try { if (pstmt != null) pstmt.close(); } catch (Exception ex) { }
			try { if (conn != null) conn.close(); } catch (Exception ex) { }
		}
		
		return reserList;
	}
}











